/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package jsf.bean.client;

import JDBCUtil.Constants;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import javax.annotation.PostConstruct;
import javax.faces.bean.ManagedBean;
import javax.faces.bean.SessionScoped;
import javax.faces.context.FacesContext;
import model.client.City;
import model.client.Country;

/**
 *
 * @author Administrator
 */
@ManagedBean
@SessionScoped
public class UpdateBean_c {

    private final Connection conn;
    //Field name constant
    private final String CITY_ID = "City_Id";
    private final String CITY_NAME = "City_Name";
    private final String COUNTRY_ID = "Country_Id";
    private final String COUNTRY_STATUS = "Country_Status";
    private final String COUNTRY_NAME = "Country_Name";

    // <editor-fold desc="Properties">
    private String errorMessage = "";
    private LoginBean_c loginBean;
    private String currPassword;
    private String newPassword;
    private String confirmPassword;
    private String email;
    private String fullName;
    private Date dateOfBirth;
    private Boolean gender;
    private float height;
    private String contactNumber;
    private int casteId;
    private String maritalStatus;
    private String familyDetails;
    private String address;
    private int religionId;
    private String motherTongue;
    private int countryId;
    private int cityId;
    private List<City> listCities;
    private List<Country> listCountries;

    // <editor-fold desc="Getters and Setters">
    
    // </editor-fold>
    
    public float getHeight() {
        return height;
    }

    public void setHeight(float height) {
        this.height = height;
    }

    public String getFamilyDetails() {
        return familyDetails;
    }

    public void setFamilyDetails(String familyDetails) {
        this.familyDetails = familyDetails;
    }
    
    public String getConfirmPassword() {
        return confirmPassword;
    }

    public void setConfirmPassword(String confirmPassword) {
        this.confirmPassword = confirmPassword;
    }

    public int getCountryId() {
        return countryId;
    }

    public void setCountryId(int countryId) {
        this.countryId = countryId;
    }

    public int getCityId() {
        return cityId;
    }

    public void setCityId(int cityId) {
        this.cityId = cityId;
    }

    public String getErrorMessage() {
        return errorMessage;
    }

    public String getNewPassword() {
        return newPassword;
    }

    public void setNewPassword(String newPassword) {
        this.newPassword = newPassword;
    }

    public String getCurrPassword() {
        return currPassword;
    }

    public void setCurrPassword(String currPassword) {
        this.currPassword = currPassword;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    public List<City> getListCities() {
        return listCities;
    }

    public List<Country> getListCountries() {
        return listCountries;
    }

    public String getMotherTongue() {
        return motherTongue;
    }

    public void setMotherTongue(String motherTongue) {
        this.motherTongue = motherTongue;
    }

    public String getMaritalStatus() {
        return maritalStatus;
    }

    public void setMaritalStatus(String maritalStatus) {
        this.maritalStatus = maritalStatus;
    }

    public int getReligionId() {
        return religionId;
    }

    public void setReligionId(int religionId) {
        this.religionId = religionId;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public String getContactNumber() {
        return contactNumber;
    }

    public void setContactNumber(String contactNumber) {
        this.contactNumber = contactNumber;
    }

    public String getFullName() {
        return fullName;
    }

    public void setFullName(String fullName) {
        this.fullName = fullName;
    }

    public Date getDateOfBirth() {
        return dateOfBirth;
    }

    public void setDateOfBirth(Date dateOfBirth) {
        this.dateOfBirth = dateOfBirth;
    }

    public Boolean getGender() {
        return gender;
    }

    public void setGender(Boolean gender) {
        this.gender = gender;
    }

    public int getCasteId() {
        return casteId;
    }

    public void setCasteId(int casteId) {
        this.casteId = casteId;
    }
    // </editor-fold>

    @PostConstruct
    public void fillUpdateFields() throws SQLException, IOException {

        loginBean = (LoginBean_c) FacesContext.getCurrentInstance()
                .getExternalContext().getSessionMap().get("loginBean1");

        //get data from Accounts & Account_Details tables by user account's username
        PreparedStatement pst = conn.prepareStatement("SELECT * FROM Accounts INNER JOIN Account_Details "
                + "ON Accounts.Account_Id = Account_Details.Account_Id "
                + "WHERE User_Name = ?");
        pst.setString(1, loginBean.getUsername());
        ResultSet rs = pst.executeQuery();
        if (!rs.next()) {
            return;
        }
        listAllCountries();

        email = rs.getString("User_Email");
        fullName = rs.getString("Full_Name");
        dateOfBirth = new Date(rs.getDate("Date_Of_Birth").getTime() + (1000 * 60 * 60 * 24));
        gender = rs.getBoolean("Gender");
        contactNumber = rs.getString("Contact_Number") + "";
        casteId = rs.getInt("Caste_Id");
        maritalStatus = rs.getString("Marital_Status");
        address = rs.getString("Address");
        religionId = rs.getInt("Religion_Type_Id");
        motherTongue = rs.getString("Mother_Tongue");
        height = rs.getFloat("Height");
        familyDetails = rs.getString("Family_Details");

        //get city and country data
        pst = conn.prepareStatement("SELECT * FROM Countries INNER JOIN Cities "
                + "ON Countries.Country_Id = Cities.Country_Id "
                + "WHERE City_Id = ?");
        pst.setInt(1, rs.getInt("City_Id"));
        rs = pst.executeQuery();
        if (!rs.next()) {
            return;
        }
        if (countryId < 1) {
            countryId = rs.getInt("Country_Id");
        }
        //country = new Country(rs.getInt("Country_Id"), rs.getBoolean("Country_Status"), rs.getString("Country_Name"));

        if (listCities == null) {
            listAllCitiesByCountryId();
        }
        if (cityId < 1) {
            cityId = rs.getInt("City_Id");
        }
        //city = new City(rs.getInt("City_Id"), rs.getInt("Country_Id"), rs.getString("City_Name"));

    }

    public void listAllCountries() throws SQLException {
        PreparedStatement pst = conn.prepareStatement("SELECT * FROM " + Constants.TABLE_COUNTRIES);
        ResultSet rs = pst.executeQuery();

        listCountries = new ArrayList<Country>();
        while (rs.next()) {
            listCountries.add(new Country(rs.getInt(COUNTRY_ID), rs.getBoolean(COUNTRY_STATUS), rs.getString(COUNTRY_NAME)));
        }

    }

    public void listAllCitiesByCountryId() throws SQLException {
        PreparedStatement pst = conn.prepareStatement("SELECT * FROM " + Constants.TABLE_CITIES
                + " WHERE " + COUNTRY_ID + " = " + countryId);

        ResultSet rs = pst.executeQuery();
        listCities = new ArrayList<City>();
        while (rs.next()) {
            listCities.add(new City(rs.getInt(CITY_ID), rs.getInt(COUNTRY_ID), rs.getString(CITY_NAME)));
        }
    }

    public void updateAcc() throws SQLException {
        if (!currPassword.equals(loginBean.getPassword())) {
            errorMessage = "Password is not correct!";
            return;
        }
        errorMessage = "";
        PreparedStatement pst = conn.prepareStatement("SELECT * FROM Accounts INNER JOIN Account_Details ON Accounts.Account_Id = Account_Details.Account_Id WHERE [User_Name] = '" + loginBean.getUsername() + "'");
        ResultSet rs = pst.executeQuery();
        if (!rs.next()) {
            return;
        }

        if ("".equals(newPassword)) {
            pst = conn.prepareStatement("UPDATE Accounts SET User_Password = '" + currPassword + "', User_Email = '" + email + "' WHERE [User_Name] = '" + loginBean.getUsername() + "'");
        } else {
            if (!confirmPassword.equals(newPassword)) {
                errorMessage = "New password and confirm password does not match.";
                return;
            }
            pst = conn.prepareStatement("UPDATE Accounts SET User_Password = '" + newPassword + "', User_Email = '" + email + "' WHERE [User_Name] = '" + loginBean.getUsername() + "'");
        }
        pst.executeUpdate();

        pst = conn.prepareStatement("UPDATE Account_Details SET "
                + "Full_Name = '" + fullName + "', "
                + "Gender = '" + gender + "', "
                + "Date_Of_Birth = '" + (new java.sql.Date(dateOfBirth.getTime())) + "', "
                + "Height = " + height + ", "
                + "Family_Details = '" + familyDetails + "', "
                + "Marital_Status = '" + maritalStatus + "', "
                + "Mother_Tongue = '" + motherTongue + "', "
                + "Contact_Number = '" + contactNumber + "', "
                + "Caste_Id = " + casteId + ", "
                + "[Address] = '" + address + "', "
                + "City_Id = " + cityId + ", "
                + "Religion_Type_Id = " + religionId
                + " FROM Account_Details INNER JOIN Accounts ON Accounts.Account_Id = Account_Details.Account_Id"
                + " WHERE [User_Name] = '" + loginBean.getUsername() + "'");        
        pst.executeUpdate();
    }

    /**
     * Creates a new instance of UpdateBean_c
     */
    public UpdateBean_c() throws SQLException, ClassNotFoundException {
        conn = JDBCUtil.MyConn.getConnection();
    }
}
